﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace DAL
{
    public class ExcelEntityDal
    {
        private readonly SQLHelper sqlhelper = null;

        public ExcelEntityDal()
        {
            sqlhelper = new SQLHelper();
        }

        #region 根据考试试题类型ID判分——王虹芸
        /// <summary>
        /// 根据考试试题类型ID进行判分
        /// </summary>
        /// <param name="excelinfo">试题信息excelinfo</param>
        /// <returns>查询结果dt</returns>
        public int UpdateExcelTypeID(ExcelQuestionRecordEntity excelrecord)
        {
            String which = WhichExcelRecored(excelrecord);
           
            string sql = "update ExcelQuestionRecordEntity_" + which + " set Fration=0 ,ExamAnswer='考生未答题' where PaperType=@PaperType and TimeStamp=@TimeStamp";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter ("@PaperType",excelrecord.PaperType ),
                new SqlParameter ("@TimeStamp",excelrecord .TimeStamp )
            };
            int flag = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);

            return flag;
        }
        #endregion

        #region 根据考试试题类型ID查询试卷——王虹芸
        /// <summary>
        /// 根据考试试题类型ID进行判分
        /// </summary>
        /// <param name="excelinfo">试题信息excelinfo</param>
        /// <returns>查询结果dt</returns>
        public DataTable QueryExcelTypeID(ExcelQuestionEntity excelinfo)
        {
            DataTable dt;
            string sql = "select * from ExcelQuestionEntity where QuestionFlag=@QuestionFlag and PaperType=@PaperType";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter ("@QuestionFlag",excelinfo.QuestionFlag ),
                new SqlParameter ("@PaperType",excelinfo.PaperType )
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);

            return dt;
        }
        #endregion

        #region 根据papertype从题库表中获取试题信息--周洲--2015年11月21日
        /// <summary>
        /// 加载excel试题信息到界面中
        /// </summary>
        /// <param name="excelinfo">学号</param>
        /// <returns>试题信息</returns>
        public DataTable LoadExcelQuestion(ExcelQuestionEntity excelinfo)
        {
            DataTable dt;
            string sql = "select * from ExcelQuestionEntity where PaperType =@PaperType order by questionid asc";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter ("@PaperType",excelinfo.PaperType),
                new SqlParameter ("@questionid",excelinfo.QuestionID),
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region excel判分之后返回信息到数据库——王虹芸
        /// <summary>
        /// 判分之后返回信息到数据库
        /// </summary>
        /// <param name="excelrecord">根据StudentID判断</param>
        /// <returns>是否添加成功</returns>
        public void ReturnExcelScore(List<ExcelQuestionRecordEntity> list, ExcelQuestionRecordEntity excelrecord)
        {
            
            String which = WhichExcelRecored (excelrecord);
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("update ExcelQuestionRecordEntity_" + which + " SET Fration = CASE QuestionID ");
            string Fation = string.Empty;
            string questionID = string.Empty;
            string studentIDs = string.Empty;
            string tamstamp = string.Empty;

            //根据学号，和时间戳选出这一段的内容，然后再根据QuestionID给每一个flag更新分数。
            #region 更新分数的拼接sql语句
            for (int i = 0; i < list.Count; i++)
            {
                questionID = list[i].QuestionID.ToString();
                //将分数传给变量
                Fation = list[i].Fration .ToString();
                //判分sql，拼接sql，用case，when语句
                sbSql.Append(" WHEN '" + questionID + "' THEN '" + Fation + "'");
            }
            sbSql.Append(" end,"); //加“，” 
            #endregion

            #region 拼接考生答案更新到数据库中
            //拼接更新考生答案sql
            sbSql.Append("  ExamAnswer = CASE QuestionID");
            for (int i = 0; i < list.Count; i++)
            {
                
                string examAnswer = list[i].ExamAnswer.ToString();
                if (examAnswer.IndexOf("'") > 0)
                {
                    examAnswer = "考生答案错误";
                }
                questionID = list[i].QuestionID.ToString();
                //学生答案信息更新，when后边的是QuerstionID,
                sbSql.Append(" WHEN '" + questionID + "' THEN '" + examAnswer + "'");
            }
            sbSql.Append(" end");   //不加逗号“，” 
            #endregion

           
            
                studentIDs += "'" + list[0].StudentID.ToString() + "'";
                tamstamp += "'" + list[0].TimeStamp.ToString() + "'";
           
            studentIDs.Remove(studentIDs.Length - 1, 1);
            sbSql.Append(" where StudentID in (" + studentIDs + ") and TimeStamp in (" + tamstamp + ")");
            DataTable dt = sqlhelper.ExecuteQuery(sbSql.ToString(), CommandType.Text);
        }
        #endregion

        #region excel查询试题类型关键字（QuestionType）——王虹芸
        /// <summary>
        /// 查询试题类型关键字
        /// </summary>
        /// <param name="excelrecord">根据QuestionTypeID判断</param>
        /// <returns></returns>
        public DataTable QueryQuestionType(ExcelQuestionEntity exceltype)
        {
            DataTable dt;
            string sql = "select QuestionFlag from ExcelQuestionEntity where PaperType=@PaperType";
            SqlParameter[] paras = new SqlParameter[] {
                  new SqlParameter ("@PaperType",exceltype.PaperType )
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 查找所有的Excel的套卷 ExcelPaperType() 邱慕夏 2015年11月20日16:57:30
        /// <summary>
        /// 查找所有的Excel的套卷
        /// </summary>
        /// <returns></returns>
        public DataTable ExcelPaperType()
        {
            DataTable dt;
            string sql = "select Distinct PaperType from ExcelQuestionEntity";
            dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        #endregion

        #region 给ExcelQuestionEntity分组 邱慕夏 2015年11月20日16:57:30
        /// <summary>
        /// 查找所有的Excel的套卷
        /// </summary>
        /// <returns></returns>
        public DataTable ExcelPaperTypeGroupByPaperType()
        {
            DataTable dt;
            string sql = "select * from ExcelQuestionEntity group by PaperType";
            dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        #endregion

        #region 根据PaperType查询分组 邱慕夏 2015年11月20日16:57:30
        /// <summary>
        ///给ExcelQutionEntity分组
        /// </summary>
        /// <returns></returns>
        public DataTable ExcelPaperTypeGroupByPaperType(String PaperType)
        {
            DataTable dt;
            string sql = "select * from ExcelQuestionEntity where PaperType=@PaperType";
            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("@PaperType",PaperType )
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion


        #region 根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// <summary>
        /// 根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// </summary>
        /// <param name="studentinfo">根据CollegeID判断学生在那个表中</param>
        /// <returns>是否添加成功</returns>
        public int InsertExcelRecord(ExcelQuestionRecordEntity studentrecord)
        {
            String which = WhichExcelRecored(studentrecord);
            string sql = "Insert into ExcelQuestionRecordEntity_" + which + "(QuestionID,StudentID,PaperType,QuestionContent,CorrectAnswer) values(@QuestionID,@StudentID,@PaperType,@QuestionContent,@RightAnswer)";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",studentrecord.StudentID ),
                new SqlParameter ("@PaperType",studentrecord.PaperType ),
                new SqlParameter ("@QuestionContent",studentrecord.QuestionContent),
                new SqlParameter ("@RightAnswer",studentrecord.CorrectAnswer),
                new SqlParameter ("@QuestionID",studentrecord.QuestionID )
            };
            int flag = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);
            return flag;
        }
        #endregion

        #region 根据学生的ID查询去重--邱慕夏  2015年11月23日14:28:57
        /// <summary>
        /// 根据学生的ID查询去重--邱慕夏--邱慕夏
        /// </summary>
        /// <param name="studentinfo">根据CollegeID判断学生在那个表中</param>
        /// <returns>是否已经添加</returns>
        public Boolean SelectExcelRecord(ExcelQuestionRecordEntity studentrecord)
        {
            String which = WhichExcelRecored(studentrecord);
            string sql = "Select * from  ExcelQuestionRecordEntity_" + which + " where StudentID=@studentID";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",studentrecord.StudentID ),
            };
            DataTable dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            if (dt.Rows.Count == 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 批量根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// <summary>
        /// 批量根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// </summary>
        /// <param name="studentinfo">根据CollegeID判断学生在那个表中</param>
        /// <returns>是否添加成功</returns>
        public int InsertExcelRecordList(List<ExcelQuestionRecordEntity> studentrecordlist, StudentInfoEntity Student)
        {
            int flag = 0;
            for (int i = 0; i < studentrecordlist.Count; i++)
            {
                String which = Student.CollegeID;
                string sql = "Insert into ExcelQuestionRecordEntity_" + which + "(QuestionID,StudentID,PaperType,QuestionContent,CorrectAnswer,TimeStamp) values(@QuestionID,@StudentID,@PaperType,@QuestionContent,@RightAnswer,@TimeStamp)";
                SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",studentrecordlist[i].StudentID ),
                new SqlParameter ("@PaperType",studentrecordlist[i].PaperType ),
                new SqlParameter ("@QuestionContent",studentrecordlist[i].QuestionContent),
                new SqlParameter ("@RightAnswer",studentrecordlist[i].CorrectAnswer),
                new SqlParameter ("@QuestionID",studentrecordlist[i].QuestionID ),
                new SqlParameter ("@TimeStamp",studentrecordlist[i].TimeStamp )
                };
                flag = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);
            }
            return flag;

           
        }
        #endregion

        #region 看看向哪个数据库表中进行填写数据 WhichExcelRecored(ExcelQuestionRecordEntity studentrecord)  邱慕夏  2015年11月23日14:14:49
        /// <summary>
        /// 看看向哪个数据库表中进行填写数据
        /// </summary>
        /// <param name="studentrecord"></param>
        /// <returns></returns>
        public String WhichExcelRecored(ExcelQuestionRecordEntity studentrecord)
        {
            DataTable dt;
            string sql = "select collegeId  from StudentInfoEntity where studentID =@studentID";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",studentrecord.StudentID ),
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            String which = dt.Rows[0][0].ToString();
            return which;
        }
        #endregion

        #region 查询该考生 是否有资格考试 -赵崇-2015年11月24日 16:39:57
        /// <summary>
        /// 查询该考生 是否有资格考试
        /// </summary>
        /// <param name="pEnStudent">学生</param>
        /// <returns>true表示可以进行考试，false表示 未进行配置 所以不能考试</returns>
        public bool GetIsCanExamByStudent(StudentInfoEntity pEnStudent)
        {

            string tableName = "";
            tableName = "ExcelQuestionRecordEntity_" + pEnStudent.CollegeID;

            //1,查询所有的选择题
            DataTable dt;
            string sql = "select * from " + tableName + " where StudentID=@StudentID";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter ("@StudentID",pEnStudent.studentID )
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);

            if (dt.Rows.Count == 0)
            {
                return false;
            }
            return true;
        }
        #endregion

        #region 配置Excel题-吕德彪-2018——陈新月——2018-5-5 17:17:39
        /// <summary>
        /// 配置Excel题
        /// </summary>
        /// <returns></returns>
        public int AddExcelQuestion(DataTable dt)
        {
            //查询数据库里有多少条数据
            ExcelEntityDal excelentity = new ExcelEntityDal();
            DataTable table = excelentity.SelectQuestionID();
            int sum = table.Rows.Count;

            string sql = "";
            int flag = 0;
            int i = 0;
            int j = 0;
            SqlParameter[] paras = null;
            for (i = 0; i < dt.Rows.Count; i++)
            {
                sql = "insert into ExcelQuestionEntity values(@QuestionID,@QuestionTypeID,@Fration,@PaperType,@QuestionFlag,@QuestionContent,@PositionX,@PositionY,@CorrectAnswer,@IsUse)";
                paras = new SqlParameter[] {
                    new SqlParameter("@QuestionID", (i + 1 + sum).ToString()),
                    new SqlParameter("@QuestionTypeID", dt.Rows[i][1].ToString()),
                    new SqlParameter("@Fration", dt.Rows[i][2].ToString()),
                    new SqlParameter("@PaperType", dt.Rows[i][3].ToString()),
                    new SqlParameter("@QuestionFlag", dt.Rows[i][4].ToString()),
                    new SqlParameter("@QuestionContent", dt.Rows[i][5].ToString()),
                    new SqlParameter("@PositionX", dt.Rows[i][6].ToString()),
                    new SqlParameter("@PositionY", dt.Rows[i][7].ToString()),
                    new SqlParameter("@CorrectAnswer", dt.Rows[i][8].ToString()),
                    new SqlParameter("@IsUse", dt.Rows[i][9].ToString())
                    };
                try
                {
                    //捕获错误数据的标志
                    flag = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);
                    j++;
                }
                catch (Exception)
                {
                    //返回错误数据的标志
                    return j;
                }
                
            }
            return j;
        }
        #endregion

        #region 查询ExcelQuestionEntity表中有多少条数据-吕德彪-2018
        /// <summary>
        /// 查询ExcelQuestionEntity表中有多少条数据
        /// </summary>
        /// <returns></returns>
        public DataTable SelectQuestionID()
        {
            string sql = "select QuestionID from ExcelQuestionEntity";
            SqlParameter[]  paras = new SqlParameter[] { };
            DataTable table = sqlhelper.ExecuteQuery(sql,paras,CommandType.Text);
            return table;
        }
        #endregion

        #region 根据试卷类型查询试卷分数配置——陈新月——2018-04-16
        public DataTable InqueryExcelInfo(ExcelQuestionEntity excelQuestion)
        {
            DataTable dt;
            string sql = "select Fration as 分值,QuestionFlag as 题型标志 from ExcelQuestionEntity where PaperType=@PaperType";
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@PaperType",excelQuestion.PaperType)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 根据试卷类型更新试卷分数——陈新月——2018-04-17
        public int UpdateExcelInfo(List<ExcelQuestionEntity> listExcel)
        {
            int UpdateExcel = 0;
            for (int i = 0; i < listExcel.Count; i++)
            {
                string sql = "update ExcelQuestionEntity set Fration=@Fration,QuestionFlag=@QuestionFlag where PaperType=@PaperType and QuestionFlag=@QuestionFlag";
                SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@PaperType",listExcel[i].PaperType),
                new SqlParameter("@Fration",listExcel[i].Fration),
                new SqlParameter("@QuestionFlag",listExcel[i].QuestionFlag)
                };
                UpdateExcel = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);

            }
            return UpdateExcel;
        }
        #endregion
    }
}
